import pymysql as sql
from flask import jsonify

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()


def getAllDepartment():
    print('return all department')
    sql = "SELECT department_id, department_name FROM department where department_id LIKE '%-%'"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有部门")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def getAllTrain():
    print('return all train')
    sql = "SELECT train_id FROM train"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有车")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def getAllMile():
    print('return all mile')
    sql = "SELECT m.mileagemark_id, m.mileagemark_name, s.station_name, e.station_name FROM mileagemark as m left join " \
          "station as s on m.start_point_id=s.station_id left join station as e on m.end_point_id=e.station_id"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有里程标")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def searchNamelist():
    sql = "SELECT component_name FROM component"

    print(sql)
    cursor.execute(sql)

    attribute = ['component_names']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)

def getAllDepartment():
    print('return all department')
    sql = "SELECT department_id, department_name FROM department where department_id LIKE '%-%'"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有部门")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def getAllTrain():
    print('return all train')
    sql = "SELECT train_id FROM train"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有车")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def getAllMile():
    print('return all mile')
    sql = "SELECT mileagemark_id, mileagemark_name, s.station_name, e.station_name FROM mileagemark as m left join " \
          "station as s on m.start_point_id=s.station_id left join station as e on m.end_point_id=e.station_id"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有里程标")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def getAllStation():
    print('return all station')
    sql = "SELECT station_id, station_name FROM station"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有站点")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def getAllCpnt():
    print('return all cpnt')
    sql = "SELECT c.type, c.component_id, c.component_name, c.attr_depart_id, c.train_id, c.mile_id, " \
          "m.mileagemark_name, c.station_id, s.station_name FROM component as c left join station as s on " \
          "c.station_id=s.station_id left join mileagemark as m on c.mile_id = m.mileagemark_id order by c.component_id"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有组分")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def deleteCpnt(word):
    sql = "Delete FROM component WHERE component_id = " + str(word)
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def newCpnt(type, dpt_id, id):
    cpnt_id = getnewnumber()
    sql = ""
    if type == 0:
        cpnt_name = str(dpt_id) + "小队车辆组分" + str(cpnt_id)
        sql = "insert into component values( '" + str(cpnt_id) + "','" + cpnt_name + "','" + str(dpt_id) + "','" + str(type) + "', '" + str(id) + "', null, null) "
    if type == 1:
        cpnt_name = str(dpt_id) + "小队里程标组分" + cpnt_id
        sql = "insert into component values( '" + cpnt_id + "','" + cpnt_name + "','" + str(dpt_id) + "','" + str(type) + "', null, '" + str(id) + "', null) "
    if type == 2:
        cpnt_name = str(dpt_id) + "小队站点组分" + cpnt_id
        sql = "insert into component values( '" + cpnt_id + "','" + cpnt_name + "','" + str(dpt_id) + "','" + str(type)  + "', null, null,'" + str(id) + "') "

    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def getnewnumber():
    sql = "select max(component_id) from component"
    cursor.execute(sql)
    data = cursor.fetchone()
    print(data)
    if data[0] is None:
        str = '001'
    else:
        number = int(data[0]) + 1
        print(number)
        str = "%03d" % number

    print(str)
    return str

def getAllStation():
    print('return all station')
    sql = "SELECT station_id, station_name FROM station"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有站点")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def searchComponent(word):
    sql = "SELECT c.type, c.component_id, c.component_name, c.attr_depart_id, c.train_id, c.mile_id, " \
          "m.mileagemark_name, c.station_id, s.station_name FROM component as c left join station as s on " \
          "c.station_id=s.station_id left join mileagemark as m on c.mile_id = m.mileagemark_id " \
          "where c.component_name like '%"+word+"%'"
    print(sql)
    cursor.execute(sql)

    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有符合条件的站点")
        return 0
    else:
        print("搜索成功")
        return temp


def deleteCpnt(word):
    sql = "Delete FROM component WHERE component_id = " + str(word)
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True

def cpntShow(name):
    sql = "SELECT train_id, station_id, mile_id FROM component WHERE component.component_name = '" + name + "'"
    # print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    d = dict()
    if temp[0][0] is not None:
        d['train'] = temp[0][0]
    else:
        d['train'] = ''

    if temp[0][1] is not None:
        sql = "SELECT station_name FROM station WHERE station_id = '" + temp[0][1] + "'"
        cursor.execute(sql)
        d['station'] = cursor.fetchall()[0][0]
    else:
        d['station'] = ''

    if temp[0][2] is not None:
        sql = "SELECT mileagemark_name FROM mileagemark WHERE mileagemark_id = '" + temp[0][2] + "'"
        cursor.execute(sql)
        d['mileMark'] = cursor.fetchall()[0][0]
    else:
        d['mileMark'] = ''

    print(d)
    return d



def getnewnumber():
    sql = "select max(component_id) from component"
    cursor.execute(sql)
    data = cursor.fetchone()
    print(data)
    if data[0] is None:
        str = '001'
    else:
        number = int(data[0]) + 1
        print(number)
        str = "%03d" % number

    print(str)
    return str
